# 路由和视图函数
from flask import Blueprint, render_template, request
from sqlalchemy import desc, and_, or_, not_

from .models import *

blue = Blueprint('user', __name__)

@blue.route('/')
def index():
    return 'index'

# 增删改查
# 增  增加数据
@blue.route('/useradd')
def user_add():
    # 添加一条数据
    # u = User() # 模型
    # u.name = 'lzr'
    # u.age = 20
    # u.sex = True
    # u.salary = 10001
    # db.session.add(u) # 将U对象添加到session中
    # db.session.commit() # 同步到数据库中

    # 同时添加多条数据
    users = []
    for i in range(10, 30):
        u = User()
        u.name = '冰冰' + str(i)
        u.age = i+1
        u.sex = True
        u.salary = 10000+i
        users.append(u)
    try:
        db.session.add(users)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        db.session.flush()
        return 'fail' + str(e)
    return 'success'


# 删 删除数据
@blue.route('/userdel')
def user_del():
    # 先找到删除的是那一条数据
    u = User.query.first() # 第一条数据
    try:
        db.session.delete(u)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        db.session.flush()
        return 'fail' + str(e)
    return 'success'


# 改 修改数据
@blue.route('/userupdate')
def user_update():
    u = User.query.first() # 查询第一条数据
    u.age = 100
    try:
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        db.session.flush()
        return 'fail' + str(e)
    return 'success'

# 查 查询数据
@blue.route('/userget')
def user_get():
    # users = User.query.all()
    # users = User.query.filter(User.age == 20)
    # users = User.query.filter_by(age=20)
    # print(list(users))
    # user = User.query.first()
    # user = User.query.filter(User.age == 100000).first_or_404()
    # counts = User.query.filter(User.age >= 10).count()
    # print(counts)
    # users = User.query.offset(3).limit(4)
    # print(list(users))

    # users = User.query.order_by(desc('age'))
    # print(list(users))

    # users = User.query.filter(User.age >= 20, User.age < 30)
    # users = User.query.filter(and_(User.age >= 20, User.age < 30))
    # users = User.query.filter(or_(User.age >=25,User.age<=20))
    # users = User.query.filter(not_(or_(User.age >=25,User.age<=20)))
    # users = User.query.filter(User.name.contains('3'))
    # users = User.query.filter(User.age.in_([10, 20, 30, 40]))
    # print(list(users))

    # users = User.query.filter(User.name.startswith('冰'))
    # users = User.query.filter(User.name.endswith('1'))


    users = User.query.filter(User.age.__gt__(25))
    print(list(users))
    return 'success'
    print(list(users))
    return 'success'



# 分页查询
@blue.route('/paginate')
def get_paginate():
    page = int(request.args.get('page', 1, type=int))
    p_size = int(request.args.get('psize', 5, type=int))

    print(page, p_size, type(p_size))

    p = User.query.paginate(page=page, per_page=p_size)
    print(p.items)

    return render_template(
        'paginate.html',
        p=p
    )



@blue.route('/adduser')
def add_user():
    u = User() # 获取模型
    try:
        u.name = 'lzr'
        u.age = 20
        u.sex = True
        u.salary=10
        db.session.add(u)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        db.session.flush()
    return 'success'

@blue.route('/updateusers')
def update_users():
    # 获取数据
    u = User.query.first()
    u.age = 100
    try:
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        db.session.flush()

    return 'success'



@blue.route('/deleteuser')
def delete_user():
    u = User.query.first()
    try:
        db.session.delete(u)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        db.session.flush()
    return 'success'